package symptomsSearch;

import ctm.javacode.dbBean;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.Iterator;
import org.json.*;

import java.net.*;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.BoldStyle;
import jxl.format.Colour;

import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.*;

@WebServlet("/symptomsSearch/symptomsMaintain.do")
public class symptomsMaintain extends HttpServlet {

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/plain");
		response.setCharacterEncoding("UTF-8");
		PrintWriter print = response.getWriter();

		
		String button_type = request.getParameter("button");

		String str = request.getParameter("str");
		String str2 = request.getParameter("str2");
		String id = request.getParameter("id");
		try {
			String Result = "";

			 if (button_type.equals("5")) {

				Result = AddNewSymptoms(str, str2);

			} else if (button_type.equals("9")) {

				Result = ModifiedSymptoms(str, str2);

			} else if (button_type.equals("10")) {

				Result = DeleteSymptoms(str);

			}  else if (button_type.equals("6")) {

				Result = ExortTable();

			}else {

				// do nothing

			}

			print.write(Result);
			print.flush();
			print.close();

		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	} // end doPost

	private static String AddNewSymptoms(String a, String b)
			throws NamingException, SQLException {
		dbBean db = new dbBean("jdbc/ctmpllab");
		db.Connected();

		String[] symptoms;
		symptoms = b.split("。");

		for (int symptoms_index = 0; symptoms_index < symptoms.length; symptoms_index++) {

			String sqlQuery_insert_to_database = "INSERT INTO pattern_to_text_table (pattern,text) VALUE ('"
					+ a + "','" + symptoms[symptoms_index] + "')";

			try {
				db.ExcuteUpdate(sqlQuery_insert_to_database);

			} catch (SQLException e) {
				throw new SQLException(e);
			} // end try-catch
		}

		String sqlQuery_delete_from_database = "DELETE FROM un_confirm_symptoms  WHERE symptom ='"
				+ a + "'";

		try {

			db.ExcuteUpdate(sqlQuery_delete_from_database);

		} catch (SQLException e) {
			throw new SQLException(e);
		} // end try-catch
		db.CloseConn();
		return "success";

	}//end AddNewStandardSymptoms

	// ------------------------------------------------------------------------------------------------------------------------------
	private static String ModifiedSymptoms(String a, String b)
			throws NamingException, SQLException {
		dbBean db = new dbBean("jdbc/ctmpllab");
		db.Connected();

		String[] symptoms;
		symptoms = b.split("。");

		String sqlQuery_delete_from_database = "DELETE FROM pattern_to_text_table  WHERE pattern ='"
				+ a + "'";

		try {

			db.ExcuteUpdate(sqlQuery_delete_from_database);

		} catch (SQLException e) {
			throw new SQLException(e);
		} // end try-catch

		for (int symptoms_index = 0; symptoms_index < symptoms.length; symptoms_index++) {

			String sqlQuery_insert_to_database = "INSERT INTO pattern_to_text_table (pattern,text) VALUE ('"
					+ a + "','" + symptoms[symptoms_index] + "')";

			try {
				db.ExcuteUpdate(sqlQuery_insert_to_database);

			} catch (SQLException e) {
				throw new SQLException(e);
			} // end try-catch
		}
		db.CloseConn();
		return "success";

	}//end ModifiedSymptoms

	// ------------------------------------------------------------------------------------------------------------------------------
	private static String DeleteSymptoms(String a) throws NamingException,
			SQLException {
		dbBean db = new dbBean("jdbc/ctmpllab");
		db.Connected();

		String sqlQuery_delete_from_database = "DELETE FROM pattern_to_text_table  WHERE pattern ='"
				+ a + "'";

		try {

			db.ExcuteUpdate(sqlQuery_delete_from_database);

		} catch (SQLException e) {
			throw new SQLException(e);
		} // end try-catch
		db.CloseConn();
		return "success";

	}//end DeleteSymptoms
	
	private static String ExortTable()
			throws NamingException, SQLException, WriteException, IOException {
		dbBean db = new dbBean("jdbc/ctmpllab");
		db.Connected();
		
		LinkedList<String> id = new LinkedList<String>();
		LinkedList<String> Symptoms = new LinkedList<String>();
		LinkedList<String> StandardizedSymptoms = new LinkedList<String>();
		
		
		String sqlQuery = "SELECT * from pattern_to_text_table"; // 資料庫原始症狀依長度排序
		ResultSet rs;
		rs = db.ExcuteQuery(sqlQuery);

		while (rs.next()) {
			
			id.add(rs.getString("id"));
			Symptoms.add(rs.getString("pattern"));
			StandardizedSymptoms.add(rs.getString("text"));

		}
		
	

		WritableWorkbook workbook = Workbook.createWorkbook(new File(
				"C:\\Users\\pllab\\Desktop\\ExportToExcel.xls"));

		WritableSheet sheet = workbook.createSheet("My Sheet", 0);

		WritableFont myFont = new WritableFont(WritableFont.createFont("標楷體"),
				14);
		myFont.setColour(Colour.BLACK);

		WritableFont titleFont = new WritableFont(
				WritableFont.createFont("標楷體"), 14);
		titleFont.setColour(Colour.BLACK);
		titleFont.setBoldStyle(WritableFont.BOLD);

		WritableCellFormat cellFormat = new WritableCellFormat();
		WritableCellFormat titleFormat = new WritableCellFormat();

		cellFormat.setFont(myFont); // 指定字型
		titleFormat.setFont(titleFont);
		titleFormat.setBackground(Colour.GRAY_25); //
		titleFormat.setAlignment(Alignment.CENTRE); // 對齊方式

		Label label00 = new Label(0, 0, "id", titleFormat);
		sheet.addCell(label00);
		Label label10 = new Label(1, 0, "原始症狀", titleFormat);
		sheet.addCell(label10);
		Label label20 = new Label(2, 0, "標準症狀", titleFormat);
		sheet.addCell(label20);

		for (int i = 0; i < id.size(); i++) {
			Label label01 = new Label(0, i + 1, id.get(i), cellFormat);
			sheet.addCell(label01);
		}

		for (int j = 0; j < Symptoms.size(); j++) {

			Label label11 = new Label(1, j + 1, Symptoms.get(j), cellFormat);
			sheet.addCell(label11);

		}
		for (int k = 0; k < StandardizedSymptoms.size(); k++) {

			Label label21 = new Label(2, k + 1, StandardizedSymptoms.get(k), cellFormat);
			sheet.addCell(label21);

		}


		workbook.write();
		workbook.close();
		db.CloseConn();
		return "success";

	}
	private static String ImportTable()
			throws NamingException, SQLException, WriteException, IOException {
		dbBean db = new dbBean("jdbc/ctmpllab");
		db.Connected();
		
		LinkedList<String> id = new LinkedList<String>();
		LinkedList<String> Symptoms = new LinkedList<String>();
		LinkedList<String> StandardizedSymptoms = new LinkedList<String>();
		
		//Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\shawn\\Desktop\\ImportFromExcel.xls"));

		
		//Sheet sheet = workbook.getSheet(0);
			

		for (int i = 0; i < id.size(); i++) {
			
		//	id.add(sheet.getCell(0, 1).getContents());
			
			
		}

		


		
		//workbook.close();
		db.CloseConn();
		return "success";

	}
	
}
